package com.esdk.sql;

import com.esdk.esdk;
import com.esdk.sql.datasource.FileConnectionPool;
import com.esdk.sql.orm.IRow;
import com.esdk.utils.*;
import lombok.NonNull;

import java.io.Serializable;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


abstract public class SqlDialect{
	private static String specificFunctionsRegex="(getAge|getDateDiff)";
	ConnMetaData cmi;

	public SqlDialect(String databaseProductName){
		this(databaseProductName,null,null);
	}

	public SqlDialect(String databaseProductName,String databaseProductVersion,Integer databaseMajorVersion){
		cmi = new ConnMetaData(databaseProductName,databaseProductVersion,databaseMajorVersion);
	}

	public SqlDialect(ConnMetaData connMetaInfo){
		cmi = connMetaInfo;
	}

	public SqlDialect(Connection conn){
		setDatabaseProductName(conn);
	}
	

	public void setDatabaseProductName(Connection conn){
		if(conn==null)
			return;
			try{
				DatabaseMetaData dbmd = conn.getMetaData();
				if(cmi==null||!dbmd.getDatabaseProductName().equals(this.cmi.databaseProductName)
					||!dbmd.getDatabaseProductVersion().equals(this.cmi.databaseProductVersion)){
					this.cmi=new ConnMetaData(dbmd.getDatabaseProductName(),dbmd.getDatabaseProductVersion(),dbmd.getDatabaseMajorVersion());
				}

			}catch(SQLException e){
				throw esdk.tool.wrapThrowble(e);
			}
	}

	private String adjustFunction(String sql){
		if(cmi.isMsSQL()){
			String result=EasyRegex.replaceAll(sql,"(dbo\\.)?"+specificFunctionsRegex,"dbo.$2",true);
			result=EasyRegex.replaceAll(result,"ifnull","isnull");
			result=EasyRegex.replaceAll(result,"now\\(\\)","getDate()");
			return result;
		}else if(!cmi.isMsSQL()){
			String result=EasyRegex.replaceAll(sql,"(dbo\\.)"+specificFunctionsRegex,"$2",true);
			result=EasyRegex.replaceAll(result,"isnull","ifnull");
			result=EasyRegex.replaceAll(result,"getDate\\(\\)","now()");
			return result;
		}
		return sql;
	}

	private String adjustTop(String sql){
		if(EasyStr.existOf(sql," top ",true)&&!cmi.isMsSQL()){
			return top2Limit(sql);
		}else if(EasyStr.existOf(sql," limit ",true)&&cmi.isMsSQL()){
			return limit2Top(sql);
		}
		return sql;
	}

	public static String top2Limit(String sql){
		if(!Pattern.compile(" top ",Pattern.CASE_INSENSITIVE).matcher(sql).find()){
			return sql;
		}
		 return new TopToLimitMString(sql).convert();
	}
	public static String limit2Top(String sql){
		if(!Pattern.compile(" limit ",Pattern.CASE_INSENSITIVE).matcher(sql).find()){
			return sql;
		}
		 return new LimitToTopMString(sql).convert();
	
	}

	public String convert(String sql,int limit, int offset){
		String result=adjustFunction(adjustTop(sql));
		if (limit > 0)
			result += (" LIMIT " + limit);
		if (offset > 0)
			result += (" OFFSET " + offset);
		return result;
	}

	static protected class TopToLimitMString extends MString{
		public TopToLimitMString(CharSequence cs){
			super(cs);
		}

		@Override protected CharSequence replace(String regex,String replacement,final CharSequence item,final MString scope){
			try{
				return esdk.regex.replaceAll(item.toString(),"(select)( +top +)(\\d+)(.*)",Pattern.CASE_INSENSITIVE,m->{
					scope.add(" limit "+m.group(3));
					return m.group(1)+m.group(4);
				});
			}catch(Exception e){
				throw new RuntimeException(e);
			}
		}

		public String convert(){
			return this.replaceAll(null,null).toString();
		}
	}

	static protected class LimitToTopMString extends TopToLimitMString{
		public LimitToTopMString(CharSequence cs){
			super(cs);
		}

		@Override protected CharSequence replace(String regex,String replacement,final CharSequence item,final MString scope){
			try{
				final String str=item.toString();
				Pattern p=Pattern.compile("(select )?(.*?)( +limit +)(\\d+)( +offset +)?(\\d+)?",Pattern.CASE_INSENSITIVE);
				Matcher m=p.matcher(str);
				if(m.find()){
					return esdk.regex.replaceAll(str,"(select )?(.*?)( +limit +)(\\d+)( +offset +)?(\\d+)?",Pattern.CASE_INSENSITIVE,matcher->{
						int top=EasyMath.toInt(matcher.group(4))+EasyMath.toInt(matcher.group(6));
						if(str.toLowerCase().startsWith("select "))
							return str.replaceAll("(select)( )","$1$2top "+top+" ").replaceAll("( +limit +)(\\d+)( +offset +)?(\\d+)?","");
						else{
							String select=scope.remove(0).toString();
							scope.add(0,select.replaceAll("(select)( )","$1$2top "+top+" "));
							return EasyStr.concat(matcher.group(1),matcher.group(2));
						}
					});
				}else {
					p=Pattern.compile("(select )?(.*?)( +limit +)(\\d+)",Pattern.CASE_INSENSITIVE);
					m=p.matcher(str);
					return esdk.regex.replaceAll(str,"(select )?(.*?)( +limit +)(\\d+)",Pattern.CASE_INSENSITIVE,matcher->{
						int top=EasyMath.toInt(matcher.group(4));
						if(str.toLowerCase().startsWith("select "))
							return str.replaceAll("(select)( )","$1$2top "+top+" ").replaceAll("( +limit +)(\\d+)","");
						else{
							String select=scope.remove(0).toString();
							scope.add(0,select.replaceAll("(select)( )","$1$2top "+top+" "));
							return EasyStr.concat(matcher.group(1),matcher.group(2));
						}
					});
				}
				
			}catch(Exception e){
				throw new RuntimeException(e);
			}
		}
	}

	public String adjustStmtSQLValue(@NonNull String sql){
		return sql.replaceAll("'","''");
	}

	public static void main(String[] args) throws Exception{
		Connection msconn=FileConnectionPool.getConnection();
 		esdk.tool.assertEquals(new JDBCTemplate(msconn,"select * from member where member_id in (select member_id from member limit 1) where (member_type='abcd') and valid=1 limit 100 offset  5").getSQL(),"select top 105 * from member where member_id in (select top 1 member_id from member) where (member_type='abcd') and valid=1");
 		esdk.tool.assertEquals(new JDBCTemplate(msconn,"select * from member where member_id in (select member_id from member limit 3 offset 5) where (member_type='abcd') and valid=1  limit 100 offset  5").getSQL(),"select top 105 * from member where member_id in (select top 8 member_id from member) where (member_type='abcd') and valid=1");
	}


	public int getDatabaseMajorVersion(){
		return cmi.databaseMajorVersion;
	}

	public String getDatabaseProductVersion(){
		return this.cmi.databaseProductVersion;
	}
  protected static StringBuilder join(Serializable... values) {
  	StringBuilder result=new StringBuilder();
  	for(Object item:values) {
  		String part=EasyObj.or(item.toString(),"");
  		if(part.length()>0) {
    		char c=part.charAt(0);
    		if(result.length()>0&&result.charAt(result.length()-1)!=' '&&c!='\r'&&c!='\n'&&c!='('&&c!=')')
    			result.append(" ");
    		result.append(part);
  		}
  	}
  	return result;
  }

  @SuppressWarnings("unused")
	public StringBuilder getSQL(Columns columns,Froms froms,Wheres wheres,String distinct,int offset
			,int limit,OrderBys orderbys,GroupBys groupbys){
		return join("SELECT",distinct,columns.assemble(),froms.assemble(),wheres.assemble(),groupbys.assemble(),orderbys.assemble());
	}
	
  @SuppressWarnings("unused")
	public StringBuilder parse(Columns columns,Froms froms,Wheres wheres,String distinct,int offset
			,int limit,OrderBys orderbys,GroupBys groupbys){
		StringBuilder result=join("SELECT",distinct,columns.assemble(),froms.assemble(),wheres.getPstmtSql(),groupbys.assemble(),orderbys.assemble());
		return result;
	}
	
	public StringBuilder parseValues(List<Object[]> values) {
		StringBuilder sb=new StringBuilder();
		sb.append(" VALUES ");
    for(Iterator<Object[]> iter=values.iterator();iter.hasNext();){
    	Object[] array=iter.next();
    	sb.append("(");
    	for(int i=0,n=array.length;i<n;i++){
    		sb.append(getStmtSqlValue(array[i]));
    		if(i<n-1)
    			sb.append(",");
    	}
    	sb.append(")");
    	if(iter.hasNext())
    		sb.append(",");
    }
    return sb;
	}
	protected StringBuilder parseValues(String tableName,String[] columns,List<Object[]> values){
		StringBuilder result=new StringBuilder();
		result.append(tableName).append(" (").append(esdk.str.arrToStr(quotaKeyword(columns))).append(")").append(" VALUES ");
		for(Iterator<Object[]> iter=values.iterator();iter.hasNext();){
			Object[] array=iter.next();
			result.append("(");
			for(int i=0,n=array.length;i<n;i++){
				result.append(getStmtSqlValue(array[i]));
				if(i<n-1)
					result.append(",");
			}
			result.append(")");
			if(iter.hasNext())
				result.append(",");
		}
		return result;
	}

	abstract public StringBuilder parseBatchReplace(String tableName,List<IRow> parentRowList,boolean ignoreRowChanged);

	public StringBuilder parseBatchInsert(String tableName,String[] fields,List<Object[]> valuesList){
			StringBuilder insertSql=new StringBuilder().append("INSERT INTO ").append(tableName).append(" (").append(esdk.str.arrToStr(quotaKeyword(fields))).append(") ");
		insertSql.append(parseValues(valuesList));
		return insertSql;
	}

	/**处理sql关键字冲突问题*/
	public String[] quotaKeyword(String[] columns){
		for(int i=0;i<columns.length;i++){
			if(esdk.str.existOf(SQLAssistant.SQLKeyWords,columns[i]))
				columns[i]="`"+columns[i]+"`";
		}
		return columns;
	}

	public String getStmtSqlValue(Object value) {
		String s;
		if(value==null)
			s="null";
       		else if(value instanceof String str) {
			s="'"+adjustStmtSQLValue(str)+"'";
		}
		else if(value instanceof Literal) {
			s=value.toString();
		}
		else if(value instanceof Field) {
			s=value.toString();
		}
		else if(value instanceof Boolean boolval) {
			if(cmi.isPostgreSQL()||cmi.isDoris())
				s=boolval?"true":"false";
			else
				s=boolval?"1":"0";
		}
		else if(value instanceof BitSet bitSet) {
			if(cmi.isPostgreSQL()||cmi.isDoris())
				s=bitSet.length()>0 && bitSet.get(0)?"true":"false";
			else
				s=bitSet.length()>0 && bitSet.get(0)?"1":"0";
		}
		else if(value instanceof Number) {
			s=value.toString();
		}
		else if(value.getClass().equals(java.sql.Time.class)) {
			s="'"+value.toString()+"'";
		}
		else if(value instanceof java.sql.Date || value instanceof cn.hutool.core.date.DateTime) {
			s="'".concat(value.toString()).concat("'");
			if(cmi.isOracle())
				s="to_date("+s+",'YYYY-MM-DD HH24:MI:SS.SSS')";
			if(s.endsWith(".000'"))
				s=s.replace(".000'","'");
		}
		else if(value.getClass().equals(java.sql.Timestamp.class)) {
			s="'"+EasyTime.format((java.sql.Timestamp)value)+"'";
			if(cmi.isOracle())
				s="to_date("+s+",'YYYY-MM-DD HH24:MI:SS.SSS')";
			if(s.endsWith(".000'"))
				s=s.replace(".000'","'");
		}
		else if(value instanceof java.util.Date dateVal) {
			s="'"+esdk.time.format(dateVal)+"'";
			if(cmi.isOracle())
				s="to_date("+s+",'YYYY-MM-DD HH24:MI:SS.SSS')";
			if(s.endsWith(".000'"))
				s=s.replace(".000'","'");

		}
		else if(value instanceof LocalDateTime localDateTime) {
			s="'"+esdk.time.format(localDateTime)+"'";
			if(s.endsWith(".000'"))
				s=s.replace(".000'","'");
		}
		else if(value instanceof LocalDate localDate) {
			s="'"+localDate+"'";
		}
/*    else if(value instanceof Object[]) {
      String[] strarr=EasyStr.toStringArray((Object[])value);
      if(strarr.length>0) {
        int dataType=(((Object[])value)[0] instanceof String)?Where.CHAR:Where.OTHER;
        s=dataType==Where.CHAR?EasySql.addSingleQuotesBySqlIn(EasyStr.arrToStr(strarr)):EasyStr.arrToStr(strarr);
        s="(".concat(s)+")";
      }else {
      	s="(null)";
      }
    }*/
		else if(value instanceof byte[] bytes){ //bit类型，如select b'1'，jdbc得到的是VARBINARY而不是bit，但是BinaryLogClient可能表示的是text类型
			if(bytes.length==1)
				s=bytes[0]+"";
			else
				s="'"+adjustStmtSQLValue(new String(bytes,StandardCharsets.UTF_8))+"'";
		}
		else if(value.getClass().isArray()) {
			s="("+getArrayValue(value)+")";
		}
		else if(value instanceof Iterable) {
			s="("+getCollectionValue((Iterable)value)+")";
		}
		else if(value instanceof Function) {
			s=value.toString();
		}
		else {
			s=value.toString();
		}
		return s;
	}

	public String getArrayValue(Object value) {
		CharAppender result=new CharAppender(',');
		if(value.getClass().isArray()&&!(value instanceof Object[])) {
			Number[] temp=EasyMath.toNumberArray(value);
			for(int i=0;i<temp.length;i++){
				result.append(temp[i].toString());
			}
		}
		else {
			Object[] temp=(Object[])value;
			for(int i=0;i<temp.length;i++) {
				result.append(getStmtSqlValue(temp[i]));
			}
		}
		if(result.isEmpty())
			result.append("NULL");
		return result.toString();
	}

	public String getCollectionValue(Iterable value) {
		CharAppender result=new CharAppender(',');
		for(Iterator iter=value.iterator();iter.hasNext();) {
			result.append(getStmtSqlValue(iter.next()));
		}
		if(result.length()==0)
			result.append("NULL");
		return result.toString();
	}

	public String getJDBCTemplateParams(Object value){
		if(value==null)
			return (String)value;
		if(value.getClass().isArray())
			return getArrayValue(value);
		else if(value instanceof Collection)
			return getCollectionValue((Collection)value);
		else
			return getStmtSqlValue(value);
	}

	public Map getJDBCTemplateParams(Map params){
		for(Iterator iter=params.entrySet().iterator();iter.hasNext();) {
			Map.Entry entry=(Map.Entry)iter.next();
			entry.setValue(getJDBCTemplateParams(entry.getValue()));
		}
		return params;
	}


	public boolean isDoris(){
		return cmi.isDoris();
	}

	public boolean isPostgreSQL(){
		return cmi.isPostgreSQL();
	}

	public boolean isMySQL(){
		return cmi.isMySQL();
	}


	public boolean isMsSQL(){
		return cmi.isMsSQL();
	}

	public boolean isHSQL() {
		return cmi.isHSQL();
	}
	public boolean isOracle(){
		return cmi.isOracle();
	}
}
